﻿CREATE PROCEDURE dbo.SP_Emails_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @Code Char (5)  =  NULL 
, @Description NVarChar (Max)  =  NULL 
, @EmailId Int   =  NULL 
, @Subject NVarChar (256)  =  NULL 
, @Template NVarChar (Max)  =  NULL 
, @TemplateResourcesPaths NVarChar (256)  =  NULL 
	AS
			WITH CTE_Emails AS
			( 
			SELECT
 							Code,
								Description,
								EmailId,
								Subject,
								Template,
								TemplateResourcesPaths,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
			 
						WHEN @OrderColume='Code' THEN Code
						 
						WHEN @OrderColume='Description' THEN Description
												WHEN @OrderColume='EmailId' THEN EmailId
						 
						WHEN @OrderColume='Subject' THEN Subject
						 
						WHEN @OrderColume='Template' THEN Template
						 
						WHEN @OrderColume='TemplateResourcesPaths' THEN TemplateResourcesPaths
									ELSE 
							    EmailId
					        	END
			) AS "RowNumber"
  			FROM Emails 
			where 
								(Code LIKE @Code OR @Code Is Null)
										AND(Description LIKE @Description OR @Description Is Null)
										AND(EmailId = @EmailId OR @EmailId Is Null)
										AND(Subject LIKE @Subject OR @Subject Is Null)
										AND(Template LIKE @Template OR @Template Is Null)
										AND(TemplateResourcesPaths LIKE @TemplateResourcesPaths OR @TemplateResourcesPaths Is Null)
								)
			SELECT  *
			FROM CTE_Emails
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			